package it.traveldream.entities;

import it.traveldream.dto.ExcursionDTO;
import it.traveldream.dto.HotelDTO.HotelRoomType;
import it.traveldream.dto.ReservationDTO;
import it.traveldream.dto.UserDTO;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;

/**
 * Entity implementation class for Entity: Reservation
 */
@Entity
@Table(name = "Reservation")

@NamedNativeQueries({
	/**
	 * These two NamedQueries retrieves the number of available spaces
	 * in a transport mean.
	 * First query searchs for the leaving date, 
	 */
	@NamedNativeQuery(
			name = "getLeavingMeanAvailabilityInDate",
			query = "SELECT sub.PRODUCTID AS ID, (sub.SPACES - sub.BOOKED) AS AVAILABILITY, `DATE` FROM ( "
					+ "SELECT t3.SPACES AS SPACES, SUM(t2.QUANTITY) AS BOOKED, "
					+ "t4.PRODUCTID AS PRODUCTID, t1.LEAVINGDATE AS `DATE` FROM "
					+ "Product t4 INNER JOIN ("
					+ "TransportMean t3 INNER JOIN ("
					+ "BookedProduct t2 INNER JOIN Reservation t1 ON t2.BOOKEDPRODUCTID = t1.GOINGMEANID"
					+ ") ON t3.PRODUCTID = t2.PRODUCTID"
					+ ") ON (t4.PRODUCTID = t3.PRODUCTID AND t4.TYPEID = t2.TYPEID) "
					+ "WHERE t4.PRODUCTID = ? "
					+ "AND t1.LEAVINGDATE = CAST(? AS DATE) "
					+ "GROUP BY t1.LEAVINGDATE)sub"
			),
	@NamedNativeQuery(
			name = "getComingBackMeanAvailabilityInDate",
			query = "SELECT sub.PRODUCTID, (sub.SPACES - sub.BOOKED) AS AVAILABILITY, `DATE` FROM ( "
					+ "SELECT t3.SPACES AS SPACES, SUM(t2.QUANTITY) AS BOOKED, "
					+ "t4.PRODUCTID AS PRODUCTID, t1.ENDDATE AS `DATE` FROM "
					+ "Product t4 INNER JOIN ("
					+ "TransportMean t3 INNER JOIN ("
					+ "BookedProduct t2 INNER JOIN Reservation t1 ON t2.BOOKEDPRODUCTID = t1.BACKMEANID"
					+ ") ON t3.PRODUCTID = t2.PRODUCTID"
					+ ") ON (t4.PRODUCTID = t3.PRODUCTID AND t4.PRODUCTID = t2.PRODUCTID) "
					+ "WHERE t4.PRODUCTID = ? "
					+ "AND t1.ENDDATE = CAST(? AS DATE) "
					+ "GROUP BY t1.ENDDATE)sub"
			),
	@NamedNativeQuery(
			/**
			 * Query returns a 4-uple regarding booked room in an hotel, grouped by type
			 * We have: Hotel ID, room type, number of total and booked rooms
			 */
			name = "getHotelBookedRoomsBetweenDates",
			query = "SELECT t0.PRODUCTID, t2.ROOMTYPE AS ROOMTYPE, "
					+ "t2.ROOMS AS TOTALROOMS, SUM(t6.QUANTITY) AS BOOKEDROOMS FROM "
					+ "HotelRooms t2 INNER JOIN ("
					+ "Product t0 INNER JOIN ("
					+ "BookedRooms t6 INNER JOIN("
					+ "Reservation t5 INNER JOIN BookedProduct t4 ON t5.HOTELID = t4.BOOKEDPRODUCTID) "
					+ "ON t6.HOTELID = t5.HOTELID) "
					+ "ON (t0.PRODUCTID = t4.PRODUCTID AND t4.TYPEID = 4)) "
					+ "ON (t2.HOTELID = t0.PRODUCTID AND t2.ROOMTYPE = t6.ROOMTYPE) "
					+ "WHERE t0.PRODUCTID = ? AND "
					+ "(t5.ENDDATE >= CAST(? AS DATE) AND t5.LEAVINGDATE <= CAST(? AS DATE)) "
					+ "GROUP BY t0.PRODUCTID, t2.ROOMTYPE"
			),
	@NamedNativeQuery(
			/**
			 * Query returns a set of 4-uple grouping hotel bookings by room type.
			 * For each hotel and each room type, we have:
			 * <ul>
			 * 	 <li>number of room available (at any time)
			 *   <li>number of room booked (between dates)
			 *</ul>
			 *
			 */
			name = "getHotelsInLocationBookedRoomsBetweenDates",
			query = "SELECT t0.PRODUCTID, t2.ROOMTYPE AS ROOMTYPE, "
					+ "t2.ROOMS AS TOTALROOMS, SUM(t6.QUANTITY) AS BOOKEDROOMS FROM "
					+ "HotelRooms t2 INNER JOIN ("
					+ "Product t0 INNER JOIN ("
					+ "BookedRooms t6 INNER JOIN("
					+ "Reservation t5 INNER JOIN BookedProduct t4 ON t5.HOTELID = t4.BOOKEDPRODUCTID) "
					+ "ON t6.HOTELID = t5.HOTELID) "
					+ "ON (t0.PRODUCTID = t4.PRODUCTID AND t4.TYPEID = 4)) "
					+ "ON (t2.HOTELID = t0.PRODUCTID AND t2.ROOMTYPE = t6.ROOMTYPE) "
					+ "WHERE t0.LOCATIONID = ? AND "
					+ "(t5.ENDDATE >= CAST(? AS DATE) AND t5.LEAVINGDATE <= CAST(? AS DATE)) "
					+ "GROUP BY t0.PRODUCTID, t2.ROOMTYPE"
    		)
})


public class Reservation implements Serializable {
	private static final long serialVersionUID = 1L;
	@Id
	@GeneratedValue
	private Long reservationId;
	
	@Temporal(javax.persistence.TemporalType.DATE)
	private Date leavingDate;
	
	@Temporal(javax.persistence.TemporalType.DATE)
	private Date endDate;
	
	@Column(nullable = false, precision=8, scale=2)
	private BigDecimal totalPrice;
	
	@ManyToOne(optional = false)
	@JoinColumn(name = "CUSTOMER", referencedColumnName = "USERNAME")
	private Customer customer;
	
	@ManyToOne(optional = false, cascade = CascadeType.ALL)
	@JoinColumn(name = "GOINGMEANID", referencedColumnName = "BOOKEDPRODUCTID", nullable = false)
	private BookedMean goingMean;
	
	@ManyToOne(optional = false, cascade = CascadeType.ALL)
	@JoinColumn(name = "BACKMEANID", referencedColumnName = "BOOKEDPRODUCTID", nullable = false)
	private BookedMean comingBackMean;
	
	@ManyToOne(optional = false, cascade = CascadeType.ALL)
	@JoinColumn(name = "HOTELID", referencedColumnName = "BOOKEDPRODUCTID", nullable = false)
	private BookedHotel hotel;
	
	@ManyToMany(cascade = CascadeType.ALL)
	@JoinTable(
			name="ReservationsExcursions",
			joinColumns = @JoinColumn(name = "RESERVATIONID", referencedColumnName = "RESERVATIONID"),
			inverseJoinColumns = @JoinColumn(name = "EXCURSIONID", referencedColumnName = "BOOKEDPRODUCTID")
	)
	private List<BookedExcursion> excursions = new ArrayList<>();

	public Reservation() {
		super();
	}   
	public Long getReservationId() {
		return this.reservationId;
	}

	public void setReservationId(Long reservationId) {
		this.reservationId = reservationId;
	}   
	public Date getLeavingDate() {
		return this.leavingDate;
	}

	public void setLeavingDate(Date leavingDate) {
		this.leavingDate = leavingDate;
	}   
	public Date getEndDate() {
		return this.endDate;
	}

	public void setEndDate(Date endDate) {
		this.endDate = endDate;
	}
	public Customer getCustomer() {
		return this.customer;
	}
	public void setCustomer(Customer customer) {
		this.customer = customer;
	}
	public BookedMean getGoingMean() {
		return this.goingMean;
	}
	public void setGoingMean(BookedMean goingMean) {
		this.goingMean = goingMean;
	}
	public BookedMean getComingBackMean() {
		return this.comingBackMean;
	}
	public void setComingBackMean(BookedMean comingBackMean) {
		this.comingBackMean = comingBackMean;
	}
	public BookedHotel getHotel() {
		return this.hotel;
	}
	public void setHotel(BookedHotel hotel) {
		this.hotel = hotel;
	}
	public List<BookedExcursion> getExcursions() {
		return this.excursions;
	}
	public void setExcursions(List<BookedExcursion> excursions) {
		this.excursions = excursions;
		
	}
	public BigDecimal getTotalPrice() {
		return this.totalPrice;
	}
	public void setTotalPrice(BigDecimal totalPrice) {
		this.totalPrice = totalPrice;
	}
	public ReservationDTO toDTO(UserDTO customer) {
		ReservationDTO dto = new ReservationDTO();
		
		dto.setReservationId(this.reservationId);
		dto.setEndDate(this.endDate);
		dto.setLeavingDate(this.leavingDate);
		dto.setTravellers(this.goingMean.getQuantity());
		dto.setTotalPrice(this.totalPrice);
		
		dto.setHotel(this.hotel.toDTO());
		HashMap<HotelRoomType, Integer> rooms = new HashMap<>(this.hotel.getBookedrooms());
		dto.setHotelRooms(rooms);
		dto.setMean(this.goingMean.toDTO());
		
		List<ExcursionDTO> exs = new ArrayList<>();
		for (BookedExcursion e : this.excursions)
			exs.add(e.toDTO());
		dto.setExcursions(exs);
		
		// can't call User.toDTO to avoid circular calls
		dto.setCustomer(customer);
		//
		return dto;
	}
   
}
